package com.pdh.dao.imp;

import com.pdh.bean.Express;
import com.pdh.bean.LazyBoardInfo;
import com.pdh.dao.BaseExpressDao;
import com.pdh.exception.DuplicateCodeException;
import com.pdh.exception.DuplicateNumberException;
import com.pdh.exception.UnHandleException;
import com.pdh.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 彭德华
 * @Date 2021-08-29 22:19
 */
public class ExpressDaoMysql extends DruidUtil implements BaseExpressDao {
    //sql语句
    /**
     * 用于查询数据库中的全部快递(总数+新增)，带取件快递(总数+新增)
     * COUNT(ID):
     * 快件总数 express_total
     * COUNT(TO_DAYS(OUT_TIME)=TO_DAYS(NOW()) OR NULL)):
     * 今日派件数 day_delivery_number   --to_days()函数计算的是从0000-00-00到现在年月日的天数
     * COUNT(STATUS=0 OR NULL):
     * 待取件数 await_express_number
     * COUNT(TO_DAYS(IN_TIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL):
     * 今日新增快件数 day_increase_number
     */
    private static final String SQL_CONSOLE_EXPRESS = "SELECT COUNT(ID) express_total,COUNT(TO_DAYS(OUT_TIME)=TO_DAYS(NOW()) OR NULL) day_delivery_count,COUNT(STATUS=1 OR NULL) await_express_total,COUNT(TO_DAYS(IN_TIME)=TO_DAYS(NOW()) AND STATUS=1 OR NULL) day_increase_count FROM EXPRESS_INFO";
    //用于查询所有快递
    private static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS_INFO";
    //用于分页查询数据库中的快递信息
    private static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS_INFO LIMIT ?,?";
    //通过取件码查询快递信息
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS_INFO WHERE CODE=?";
    //通过快递单号查询快递信息
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS_INFO WHERE NUMBER=?";
    //通过录入人手机号查询快递信息
    private static final String SQL_FIND_BY_COURIER_PHONE = "SELECT * FROM EXPRESS_INFO WHERE COURIER_PHONE=?";
    //通过用户手机号查询快递信息
    private static final String SQL_FIND_BY_USER_PHONE = "SELECT * FROM EXPRESS_INFO WHERE USER_PHONE=?";
    //通过用户手机号和状态码查询快递信息
    private static final String SQL_FIND_BY_USER_PHONE_AND_STATUS = "SELECT * FROM EXPRESS_INFO WHERE USER_PHONE=? AND STATUS = ?";
    //录入快递
    private static final String SQL_INSERT = "INSERT INTO EXPRESS_INFO (NUMBER,USERNAME,USER_PHONE,COMPANY,CODE,IN_TIME,STATUS,COURIER_PHONE,CREATE_TIME) VALUES(?,?,?,?,?,NOW(),1,?,NOW());";
    //快递的修改 单号、姓名、电话号码、公司、状态码
    private static final String SQL_UPDATE = "UPDATE EXPRESS_INFO SET NUMBER=?,USERNAME=?,USER_PHONE=?,COMPANY=?,STATUS=?,UPDATE_TIME=NOW() WHERE ID=?";
    //更新快递状态 0 审核中,1 待取件,2 已取件,3 已取消,4 已删除,5 异常
    private static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS_INFO SET STATUS=?,OUT_TIME=NOW(),CODE=NULL WHERE CODE=?";
    //根据id 快递删除
    private static final String SQL_DELETE = "UPDATE EXPRESS_INFO SET STATUS=4,CODE=NULL WHERE ID=?";

    //懒人排行榜 总榜、年榜、月榜
    private static final String SQL_LAZY_BOARD_COUNT = "SELECT COUNT(ID) EXPRESS_COUNT,USER_PHONE,USERNAME FROM EXPRESS_INFO GROUP BY USER_PHONE,USERNAME ORDER BY EXPRESS_COUNT DESC LIMIT ?,?";
    private static final String SQL_LAZY_BOARD_YEAR = "SELECT COUNT(ID) EXPRESS_COUNT,USER_PHONE,USERNAME FROM EXPRESS_INFO WHERE STATUS=1 AND YEAR(IN_TIME)=YEAR(NOW()) GROUP BY USER_PHONE,USERNAME ORDER BY EXPRESS_COUNT DESC LIMIT ?,?";
    private static final String SQL_LAZY_BOARD_MONTH = "SELECT COUNT(ID) EXPRESS_COUNT,USER_PHONE,USERNAME FROM EXPRESS_INFO WHERE STATUS=1 AND DATE_FORMAT(IN_TIME,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY USER_PHONE,USERNAME ORDER BY EXPRESS_COUNT DESC LIMIT ?,?";



    //把数据库连接、sql语句配置和查询的数据 实例化向上抽取，避免代码冗余
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;


    //封装一些简单的方法 获取一个新的express类 在此之前必须确定resultSet为非空！！
    private static final Express getNewExpress(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        String number = resultSet.getString("number");
        String userPhone = resultSet.getString("user_phone");
        String username = resultSet.getString("username");
        String company = resultSet.getString("company");
        String code = resultSet.getString("code");
        Timestamp inTime = resultSet.getTimestamp("in_time");
        Timestamp outTime = resultSet.getTimestamp("out_time");
        int status = resultSet.getInt("status");
        String courier_phone = resultSet.getString("courier_phone");
        return new Express(id, number, userPhone, username, company, code, inTime, outTime, status, courier_phone);
    }

    //针对lazyBoardInfo封装一个方法
    private static final List<LazyBoardInfo> getLazyBoardInfoList(Connection connection,PreparedStatement statement,ResultSet resultSet,String sql,int offset,int limit) throws UnHandleException {
        List<LazyBoardInfo> list = new ArrayList<>();
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,offset);
            statement.setInt(2,limit);
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            while (resultSet.next()) {//首先明确得到的是些什么数据，而不是照着教程敲
                int expressCount = resultSet.getInt("express_count");
                String userPhone = resultSet.getString("user_phone");
                String username = resultSet.getString("username");
                LazyBoardInfo lazyCount = new LazyBoardInfo(userPhone,username,expressCount);
                list.add(lazyCount);
            }

        } catch (SQLException e) {
            throw new UnHandleException(e.getMessage());
        } finally {
        }
        return list;
    }


    /**
     * 用于查询数据库中的全部快递(总数+新增)，带取件快递(总数+新增)
     *
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public Map<String, Integer> consoleExpress() {
        Map<String, Integer> data = new HashMap<>();
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_CONSOLE_EXPRESS);
            //3.填充参数(可选)
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            if (resultSet.next()) {//首先明确得到的是些什么数据，而不是照着教程敲
                //express_total day_delivery_number await_express_number day_increase_number
                int expressTotal = resultSet.getInt("express_total");
                int dayDeliveryCount = resultSet.getInt("day_delivery_count");//
                int awaitExpressTotal = resultSet.getInt("await_express_total");//
                int dayIncreaseCount = resultSet.getInt("day_increase_count");//
                data.put("expressTotal", expressTotal);
                data.put("dayDeliveryCount", dayDeliveryCount);
                data.put("awaitExpressTotal", awaitExpressTotal);
                data.put("dayIncreaseCount", dayIncreaseCount);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return data;
    }


    /**
     * 用于查询所有快递
     *
     * @param paging      是否分页标记 true/false 分页/查询所有
     * @param offset     sql语句起始索引
     * @param limit         页查询的数量
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean paging, int offset, int limit) {
        //0.把一个快递信息封装一个整体，以链表的形式存储
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库的连接
        //已经抽取到全局
        connection = getConnection();
        //2.预编译sql语句
        try {
            //
            if (paging) {
                statement = connection.prepareStatement(SQL_FIND_LIMIT);
                //3.填充参数(可选)
                statement.setInt(1, offset);
                statement.setInt(2, limit);
            } else {
                statement = connection.prepareStatement(SQL_FIND_ALL);
            }
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            while (resultSet.next()) {
                Express express = getNewExpress(resultSet);
                data.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return data;
    }

    /**
     * 根据快递单号查询快递信息
     *
     * @param number 单号
     * @return 查询的快递信息，单号不存在时候返回null
     */
    @Override
    public Express findByNumber(String number) {
        //1.获取数据库的连接
        //已经抽取到全局
        connection = getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.填充参数(可选)
            statement.setString(1, number);
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            if (resultSet.next()) {//获取的是单个对象
                return getNewExpress(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return null;
    }

    /**
     * 根据快递取件码查询快递信息
     *
     * @param code 取件码
     * @return 查询的快递信息，取件码不存在时候返回null
     */
    @Override
    public Express findByCode(String code) {
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_CODE);
            //3.填充参数(可选)
            statement.setString(1, code);
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            if (resultSet.next()) {//获取的是单个对象
                return getNewExpress(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return null;
    }

    /**
     * 根据用户手机号码，查询他所有的快递信息
     *
     * @param userPhone 用户手机号码
     * @return 查询的快递信息列表，不存在时候返回null
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        List<Express> data = new ArrayList<>();
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_USER_PHONE);
            //3.填充参数(可选)
            statement.setString(1, userPhone);
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            while (resultSet.next()) {//循环遍历获取数据
                Express express = getNewExpress(resultSet);
                data.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return data;
    }

    /**
     * 根据用户手机号码，和状态码，查询他的指定状态的所有快递信息
     *
     * @param userPhone 用户手机号码
     * @param status    快件状态码
     * @return 查询的快递信息列表，不存在时候返回null
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_USER_PHONE_AND_STATUS);
            //3.填充参数(可选)
            statement.setString(1, userPhone);
            statement.setInt(2, status);
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            while (resultSet.next()) {//循环遍历获取数据
                Express express = getNewExpress(resultSet);
                data.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return data;
    }

    /**
     * 根据用户手机号码，查询录入的所有快递记录
     *
     * @param sysPhone 录入人手机号码
     * @return 查询的快递信息列表，不存在时候返回null
     */
    @Override
    public List<Express> findByCourierPhone(String sysPhone) {
        ArrayList<Express> data = new ArrayList<>();
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_FIND_BY_COURIER_PHONE);
            //3.填充参数(可选)
            statement.setString(1, sysPhone);
            //4.执行sql语句
            resultSet = statement.executeQuery();//执行查询语句
            //5.获取执行结果
            while (resultSet.next()) {//循环遍历获取数据
                Express express = getNewExpress(resultSet);
                data.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, resultSet);
        }
        return data;
    }

    /**
     * @param express 录入的快递对象
     * @return 录入结果 true/false 成功/失败
     */
    @Override
    public boolean insert(Express express) throws DuplicateCodeException, DuplicateNumberException {
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(SQL_INSERT);
            //3.填充参数
            statement.setString(1, express.getNumber());
            statement.setString(2, express.getUsername());
            statement.setString(3, express.getUserPhone());
            statement.setString(4, express.getCompany());
            statement.setString(5, express.getCode());
            statement.setString(6, express.getCourierPhone());
            //4.执行sql语句并获取执行结果
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            //对异常进行处理
            if (e.getMessage().endsWith("for key 'uk_code'")) {
                //1.是因为取件码重复 而出现了异常
                DuplicateCodeException codeException = new DuplicateCodeException(e.getMessage());
                throw codeException;
            } else if(e.getMessage().endsWith("for key 'uk_number'")) {
                DuplicateNumberException numberException = new DuplicateNumberException(e.getMessage());
                throw numberException;
            }else {
                e.printStackTrace();
            }
        } finally {
            //5.释放资源
            close(connection, statement, null);
        }
        return false;
    }

    /**
     * 快递的修改
     *
     * @param id         要修改的快递id
     * @param newExpress 新的快递对象(包括：number,company,username,userPhone)
     * @return 修改结果 true/false 成功/失败
     */
    @Override
    public boolean update(int id, Express newExpress) throws DuplicateNumberException {
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_UPDATE);
            //3.填充参数(可选)
            //NUMBER=?,USERNAME=?,USER_PHONE=?,COMPANY=?,STATUS=? WHERE ID=?
            statement.setString(1, newExpress.getNumber());
            statement.setString(2, newExpress.getUsername());
            statement.setString(3,newExpress.getUserPhone());
            statement.setString(4, newExpress.getCompany());
            statement.setInt(5, newExpress.getStatus());
            statement.setInt(6, id);
            //4.执行sql语句
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            /*if(e.getMessage().endsWith("for key 'uk_number'")){
                //number重复
                DuplicateNumberException numberException = new DuplicateNumberException(e.getMessage());
                throw numberException;
            }else {
                e.printStackTrace();
            }*/
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, null);
        }
        return false;
    }

    /**
     * 0 审核中,1 待取件,2 已取件,3 已取消,4 已删除,5 异常
     * 更改快递的状态为2，表示取件完成
     * @param code 要修改的快递单号
     * @return true/false 成功/失败
     */
    @Override
    public boolean updateStatus(String code,int status) {
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_UPDATE_STATUS);
            //3.填充参数(可选)
            statement.setInt(1,status);
            statement.setString(2, code);
            //4.执行sql语句
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, null);
        }
        return false;
    }

    /**
     * 根据id删除单个快递信息
     *
     * @param id 要删除的快递id
     * @return true/false 成功/失败
     */
    @Override
    public boolean delete(int id) {
        //1.获取数据库的连接
        //已经抽取到全局
        connection = DruidUtil.getConnection();
        //2.预编译sql语句
        try {
            statement = connection.prepareStatement(SQL_DELETE);
            //3.填充参数(可选)
            statement.setInt(1, id);
            //4.执行sql语句
            return statement.executeUpdate() > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源释放
            close(connection, statement, null);
        }
        return false;
    }



    /**
     * 于查询总懒人排行榜 包括总榜、年榜、月榜
     *lazyBoardCount,lazyBoardYear,lazyBoardMonth
     * @param paging
     * @param offset
     * @param limit
     * @return list
     */
    @Override
    public Map<String,List<LazyBoardInfo>>  lazyBoardInfo(boolean paging, int offset, int limit) throws UnHandleException {
        Map<String,List<LazyBoardInfo>> map = new HashMap<>();
        //1.获取数据库的连接
        connection = getConnection();
        if(paging) {
            List<LazyBoardInfo> lazyBoardCount = getLazyBoardInfoList(connection, statement, resultSet, SQL_LAZY_BOARD_COUNT, offset, limit);
            List<LazyBoardInfo> lazyBoardYear = getLazyBoardInfoList(connection, statement, resultSet, SQL_LAZY_BOARD_YEAR, offset, limit);
            List<LazyBoardInfo> lazyBoardMonth = getLazyBoardInfoList(connection, statement, resultSet, SQL_LAZY_BOARD_MONTH, offset, limit);
            map.put("lazyBoardCount", lazyBoardCount);
            map.put("lazyBoardYear", lazyBoardYear);
            map.put("lazyBoardMonth", lazyBoardMonth);
        } else {
            //2.不分页方法不提供 默认未null
        }
        close(connection, statement, resultSet);

        return map;
    }
}
